package top.codedance.iotp.common.util.db;

import top.codedance.iotp.common.entity.UserEntity;
import top.codedance.iotp.common.util.AsymmetricEncryption;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class UserDaoUtil implements DaoUtil<UserEntity> {

    private JdbcUtilAdapter jdbcUtilAdapter;

    public UserDaoUtil(Class<?> clazz, Logger logger, Properties properties) {
        this.jdbcUtilAdapter = new JdbcUtilAdapter(clazz, logger, properties);
        try {
            createTable();
        } catch (SQLException throwables) {
            logger.debug("table already exists");
        }
    }

    public Connection getConnection(){
        Connection c = null;
        try {
            while (c == null || c.isClosed()) {
                c = jdbcUtilAdapter.getConnection();
                try {
                    Thread.sleep(500);
                }catch (Exception e){}
            }
        }catch (Exception ex){}
        return c;
    }

    public void returnConnection(Connection c) {
        jdbcUtilAdapter.returnConnection(c);
    }

    public void destory() {
        jdbcUtilAdapter.destory();
    }

    public void createTable() throws SQLException {
        Connection c = getConnection();
        Statement stmt = c.createStatement();
        if(jdbcUtilAdapter.getDbType() == JdbcUtilAdapter.DB_TYPE.SQLITE) {
            String sql = "CREATE TABLE users ("
                    + " id INTEGER primary key AUTOINCREMENT,"
                    + " username CHAR(50) NOT NULL UNIQUE,"
                    + " password CHAR(600) NOT NULL,"
                    + " device_id CHAR(600) NOT NULL UNIQUE,"
                    + " is_admin TINYINT(1) NOT NULL DEFAULT 0"
                    + " );";
            stmt.executeUpdate(sql);
        }
        if(jdbcUtilAdapter.getDbType() == JdbcUtilAdapter.DB_TYPE.MYSQL) {
            String sql = "CREATE TABLE users ("
                    + " id INTEGER primary key AUTO_INCREMENT,"
                    + " username VARCHAR(50) NOT NULL UNIQUE,"
                    + " password VARCHAR(600) NOT NULL,"
                    + " device_id VARCHAR(600) NOT NULL UNIQUE,"
                    + " is_admin TINYINT(1) NOT NULL DEFAULT 0"
                    + " );";
            stmt.executeUpdate(sql);
        }
        stmt.close();
        returnConnection(c);
    }

    public void add(UserEntity userEntity) throws SQLException {
        Connection c = getConnection();
        Statement stmt = c.createStatement();
        c.setAutoCommit(false);
        StringBuffer sb = new StringBuffer();
        String id = userEntity.getId() != 0 ? String.valueOf(userEntity.getId()) : "NULL";
        sb.append("INSERT INTO users (id, username, password, device_id, is_admin) "
                + "VALUES (" + id + ", '" + userEntity.getUsername() + "', '" + userEntity.getPassword() + "', '" + userEntity.getDeviceId() + "', " + userEntity.isAdmin() + ");");
        stmt.execute(sb.toString());
        c.commit();
        stmt.close();
        returnConnection(c);
    }

    public boolean has(UserEntity userEntity) throws SQLException {
        Connection c = getConnection();
        Statement stmt = c.createStatement();
        boolean flag = false;
        ResultSet rs = stmt.executeQuery("SELECT count(1) AS num FROM users WHERE username='" + userEntity.getUsername() + "' and password='" + userEntity.getPassword() + "' and device_id='" + userEntity.getDeviceId() + "';");
        if (rs.next()) {
            flag = rs.getInt("num") > 0;
        }
        rs.close();
        stmt.close();
        returnConnection(c);
        return flag;
    }

    @Override
    public boolean has(UserEntity entity, String privateKey) throws SQLException {
        UserEntity newEntity = getEntity(entity, privateKey);
        if(newEntity != null){
            try {
                return newEntity.getPassword().equals(entity.getPassword());
            }catch (Exception e){
                return false;
            }
        }else{
            return false;
        }
    }

    public boolean hasAdmin(UserEntity userEntity) throws SQLException{
        Connection c = getConnection();
        Statement stmt = c.createStatement();
        boolean flag = false;
        ResultSet rs = stmt.executeQuery("SELECT count(1) AS num FROM users WHERE username='" + userEntity.getUsername() + "' and password='" + userEntity.getPassword() + "' and is_admin=1;");
        if (rs.next()) {
            flag = rs.getInt("num") > 0;
        }
        rs.close();
        stmt.close();
        returnConnection(c);
        return flag;
    }

    @Override
    public boolean hasAdmin(UserEntity entity, String privateKey) throws SQLException {
        UserEntity newEntity = getEntity(entity, privateKey);
        if(newEntity != null){
            try {
                return newEntity.getPassword().equals(entity.getPassword()) && newEntity.isAdmin();
            }catch (Exception e){
                return false;
            }
        }else{
            return false;
        }
    }

    /**
     * 获取实体
     * @param userEntity
     * @return
     * @throws SQLException
     */
    public UserEntity getEntity(UserEntity userEntity) throws SQLException {
        Connection c = getConnection();
        UserEntity entity = null;
        Statement stmt = c.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE username='" + userEntity.getUsername() + "'");
        if (rs.next()) {
            entity = new UserEntity();
            entity.setId(rs.getInt("id"));
            entity.setUsername(rs.getString("username"));
            entity.setDeviceId(rs.getString("device_id"));
            entity.setPassword(rs.getString("password"));
            entity.setAdmin(rs.getBoolean("is_admin"));
        }
        rs.close();
        stmt.close();
        returnConnection(c);
        return entity;
    }

    @Override
    public UserEntity getEntity(UserEntity userEntity, String privateKey) throws SQLException {
        Connection c = getConnection();
        UserEntity entity = null;
        Statement stmt = c.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE username='" + userEntity.getUsername() + "'");
        if (rs.next()) {
            entity = new UserEntity();
            entity.setId(rs.getInt("id"));
            entity.setUsername(rs.getString("username"));
            try {
                entity.setDeviceId(AsymmetricEncryption.decrypt(rs.getString("device_id"), privateKey));
            }catch (Exception e){
                entity.setDeviceId(rs.getString("device_id"));
            }
            try {
                entity.setPassword(AsymmetricEncryption.decrypt(rs.getString("password"), privateKey));
            }catch (Exception e){
                entity.setPassword(rs.getString("password"));
            }
            entity.setAdmin(rs.getBoolean("is_admin"));
        }
        rs.close();
        stmt.close();
        returnConnection(c);
        return entity;
    }

    public List<UserEntity> selectAll() throws SQLException {
        Connection c = getConnection();
        Statement stmt = c.createStatement();
        List<UserEntity> userEntities = new ArrayList<UserEntity>();
        ResultSet rs = stmt.executeQuery("SELECT * FROM users ORDER BY id DESC;");
        while (rs.next()) {
            UserEntity entity = new UserEntity();
            entity.setId(rs.getInt("id"));
            entity.setUsername(rs.getString("username"));
            entity.setDeviceId(rs.getString("device_id"));
            entity.setPassword(rs.getString("password"));
            entity.setAdmin(rs.getBoolean("is_admin"));
            userEntities.add(entity);
        }
        rs.close();
        stmt.close();
        returnConnection(c);
        return userEntities;
    }

    @Override
    public List<UserEntity> selectAll(String privateKey) throws SQLException {
        Connection c = getConnection();
        Statement stmt = c.createStatement();
        List<UserEntity> userEntities = new ArrayList<UserEntity>();
        ResultSet rs = stmt.executeQuery("SELECT * FROM users ORDER BY id DESC;");
        while (rs.next()) {
            UserEntity entity = new UserEntity();
            entity.setId(rs.getInt("id"));
            entity.setUsername(rs.getString("username"));
            try {
                entity.setDeviceId(AsymmetricEncryption.decrypt(rs.getString("device_id"), privateKey));
            }catch (Exception e){
                entity.setDeviceId(rs.getString("device_id"));
            }
            try {
                entity.setPassword(AsymmetricEncryption.decrypt(rs.getString("password"), privateKey));
            }catch (Exception e){
                entity.setPassword(rs.getString("password"));
            }
            entity.setAdmin(rs.getBoolean("is_admin"));
            userEntities.add(entity);
        }
        rs.close();
        stmt.close();
        returnConnection(c);
        return userEntities;
    }

    public void update(UserEntity userEntity) throws SQLException {
        Connection c = getConnection();
        Statement stmt = c.createStatement();
        c.setAutoCommit(false);
        String sql = "UPDATE users set username = '" + userEntity.getUsername() + "' and password='" + userEntity.getPassword() + "' and device_id = '" + userEntity.getDeviceId() + "' and is_admin=" + userEntity.isAdmin() + " where ID=" + userEntity.getId() + ";";
        stmt.executeUpdate(sql);
        c.commit();
        stmt.close();
        returnConnection(c);
    }

    public void delete(int id) throws SQLException {
        Connection c = getConnection();
        Statement stmt = c.createStatement();
        c.setAutoCommit(false);
        String sql = "DELETE from users where id=" + id + ";";
        stmt.execute(sql);
        c.commit();
        stmt.close();
        returnConnection(c);
    }
}
